I'm trying to insert the contents of an Excel file into my database using a .NET application. Now, I am able to load the contents of the Excel file into my application and into a datagrid. However, the contents of the Excel file are old. Everything in the file was stored as strings whereas the database holds them as IDs with corresponding strings. So I need to go to the database, find the matching string and return the corresponding ID. Thing is, I don't know how to traverse a DataGrid or anything like that. If anyone can help, it would be great
Eoin
you can use either regex or SQL- to do the matching. since i really cant see how this excel file. SQL is pretty basic- this is going to be slow if there is alot of data
have 2 data readers 1 with string to match against and 1 to correspond to the string you are pulling from excel... since you are matcing partial information im guessing youll probubly have to tokenize it( several methods for this- but kinda costly with this extra step )
regex will work a little better.. i dont know what your plan is but if you are planning to have this excel file queried multiple times as a working db, you might want to reconsider, and have a script rewrite everything to a db table based on string matches.
maybe have it run through, check if IsMatch = true
then have it ad or return your ID
I think you may have misunderstood what I was asking. The comparison will be fine. I don't know how to set up for comparison though. I can import the information from the Excel file into a DataGrid and I can retrieve the table that contains the mappings between the IDs and descriptions from the database into an ArrayList. The thing is, I don't know how to compare the items.
For Each objRefItem In RefItem
For i = 0 To myDataSet.Tables(0).Rows.Count
If myDataSet.Tables(0).Rows.Contains(objRefItem.Name)
Then
MsgBox(objRefItem.ItemID.ToString + " " + objRefItem.Name)
End If
Next
Next
Where RefItem contains the list of all IDs and descriptions, myDataSet is the dataset that holds the contents of the Excel file. I am OK with Collections but the DataSet & DataGrid aren't collections so I don't know how to traverse them...
in c# you can use
type reader to pull 1 value from dataset and then run query on that set. basically in you loop you have to have each string in excel file run an sql query to your db if match is good it will return the id.
i see your loop and in you loop you have each string searched for in db with id associated.
otherwise im still dont know what you are trying to do
OK I took a different approach to it. I managed to convert my DataSet to an ArrayList and I already had the mapping table in an ArrayList so it was simply a matter of iterating through both of them and updating the data from the Excel file as it went along. So now I'm in the process of inserting them into the database. It won't let me pass an ArrayList to my asmx file though...any idea why?